• Создал(а) Неизвестный пользователь (pavel.shevchenko), редактировал(а) Неизвестный пользователь (a.s.voronin@rt-sk.ru) янв 07, 2025

Сервис предназначен для получения s3-ссылки на архив, который содержит выгрузки из БД для аналитического кластера


Сервис случшает топик mz.petition.in.request

Сервис отправляет ответ в топик mz.petition.in.response


request-class: request@urn://rostelekom.ru/ERVU-analyticalCluster/1.0.1
response-class: response@urn://rostelekom.ru/ERVU-analyticalCluster/1.0.1

На любой запрос сервис возвращает ответ вида:

?
<ns:response xmlns:ns="urn://rostelekom.ru/ERVU-analyticalCluster/1.0.1">
    <ns:responseData>
        <ns:extractRegistry>
            <ns:FileName>analytics_dump_20231106.delta.zip</ns:FileName>
            <ns:FileType>application/zip</ns:FileType>
            <ns:fileDateTime>2023-11-07T11:52:55.512533158</ns:fileDateTime>
        </ns:extractRegistry>
    </ns:responseData>
</ns:response>

Файл analytics_dump_20231106.delta.zip всегда содержит данные выгрузок за прошлый день (4 csv-файла).

Выгрузка данных происходит на сервере БД каждый день, выгрузку выполняет cron-job по расписанию в 05:00.

Виды выгрузок


По инцидентам
?
SELECT
 inc.id AS "ID инцидента",
 inc.recruit_id AS "Гражданин(ID РВУ)",
 TO_CHAR(hist.create_date, 'DD.MM.YYYY HH:mi') AS "Время изменения INC",
 hist.status AS "Статус INC",
 inc.object AS "Что привело к INC",
 inc.text AS "Описание INC",
 inc.reason AS "Причина INC",
 inc."number" AS "Номер инцидента"
FROM incident inc
 LEFT JOIN incident_history hist ON inc.id = hist.incident_id
WHERE
 DATE_TRUNC('day', hist.create_date) = 'yesterday'::TIMESTAMP;
По рекрутам
?
SELECT rct.id                                              AS "Гражданин(ID РВУ)",
       rct.gender                                          AS "Пол",
       EXTRACT('YEAR' FROM AGE(NOW(), rct.birth_date))     AS "Возраст",
       COALESCE((rct.addresses #>> '{0, regionCode}'), '') AS "Регион проживания(МЖ)",
       COALESCE((rct.addresses #>> '{1, regionCode}'), '') AS "Регион проживания(МП)",
       COALESCE((rct.addresses #>> '{2, regionCode}'), '') AS "Регион проживания(МН)",
       CASE
           WHEN jsonb_path_exists(rct_info.info, '$.postponements[*].delayCode ? (@ != null)') = TRUE
               THEN 'Да'
           ELSE 'Нет' END                                  AS "Отсрочка/Освобождение",
       rct_info.info -> 'spouse' ->> 'countLittleChildren' AS "Дети",
       CASE
           WHEN rct_info.info -> 'spouse' ->> 'status' = 'Б' THEN 'Состоит в браке'
           WHEN rct_info.info -> 'spouse' ->> 'status' = '0' THEN 'Не состоит в браке'
           ELSE '-' END                                    AS "Брак",
       TO_CHAR(hist.date_time, 'DD.MM.YYYY HH:mi')         AS "Время",
       hist.status                                         AS "Статус",
       hist.event                                          AS "Событие"
FROM recruits rct
         LEFT JOIN recruits_info rct_info ON rct.id = rct_info.recruit_id
         LEFT JOIN recruits_history hist ON rct.id = hist.recruit_id
WHERE DATE_TRUNC('day', hist.date_time) = 'yesterday'::TIMESTAMP;
По ограничениям
?
SELECT
    rdoc.id      AS "ID документа measure",
    rdoc_item.id AS "ID measure",
    r.id         AS "ID measure справочника",
    r.name       AS "Name measure справочника",
    rdoc.subpoena_id AS "ID повестки",
    rdoc.vk_id AS "ID военкомата",
    TO_CHAR(rdoc_item.updated_at, 'DD.MM.YYYY HH:mi') AS "Дата изменения measure",
    rdoc_item.status AS "Статус measure",
    rdoc.type AS "Тип документа measure",
    rdoc_item.restriction_document_create_id AS "ID документа введения measure",
    rdoc_item.restriction_document_cancel_id AS "ID документа отмены measure"
FROM
    restriction_document rdoc,
    restriction_document_item rdoc_item,
    restriction r
WHERE
    (rdoc.id = rdoc_item.restriction_document_create_id OR rdoc.id = rdoc_item.restriction_document_cancel_id)
  AND r.id = rdoc_item.restriction_id
  AND DATE_TRUNC('day', rdoc_item.updated_at) = 'yesterday'::TIMESTAMP;
По повесткам
?
SELECT sbp.id                                           AS "ID повестки",
       TO_CHAR(hist.date_time, 'DD.MM.YYYY HH:mi')            AS "Дата изменения повестки",
       sbp.recruit_id                                   AS "Гражданин(ID РВУ)",
       sbp.department_id                                AS "ID военкомата",
       sbp.delivery_info::json ->> 'deliveryType'       AS "Тип направления повестки",
       hist.status_id                                   AS "Статус повестки",
       sbp_stat.id                                      AS "ID Статус повестки",
       sbp_stat.code                                    AS "Код статуса повестки"
FROM subpoena sbp
         LEFT JOIN subpoena_history hist ON sbp.id = hist.subpoena_id
         LEFT JOIN subpoena_status sbp_stat ON hist.status_id = sbp_stat.id
WHERE DATE_TRUNC('day', hist.date_time) = 'yesterday'::TIMESTAMP;
Написать комментарий...